/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package delta.productcatalog.web.reportingOnOrders;

import entity.Orders;
import entity.Users;
import facade.UsersFacadeLocal;
import facade.WarehouseFacadeLocal;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * Class, which contents methods for export reports to Excel
 *
 * @author Ivchenko Oleg
 */
public class ExportRepToExcel {
    private WarehouseFacadeLocal warehouseFacade = lookupWarehouseFacadeLocal();
    private UsersFacadeLocal usersFacade = lookupUsersFacadeLocal();
    private static String repStatisticOrdFileName = "StatisticReportOnOrders";/* создаем статически переменные для названия файлов с различными отчетами*/
    private static String repAllOrdFileName = "ReportAllOrders";
    private static String repProductFileName = "ReportsOnProduct";
    private static String currentRepDate;
    private final static String FILE_ENDING = ".xls";
    //private static File repStatOrdTemplaitPath = new File("templaits/StatisticReportOnOrders.xls");
    //private static String repStatOrdTemplaitPath = "/web/templaits/StatisticReportOnOrders.xls";

    public static String getRepAllOrdFileName() {
        return repAllOrdFileName + currentRepDate + FILE_ENDING;
    }

    public static String getrepStatisticOrdFileName() {
        return repStatisticOrdFileName + currentRepDate + FILE_ENDING;
    }

    public static String getRepProductFileName() {
        return repProductFileName + currentRepDate + FILE_ENDING;
    }
    
    public void exportRepOnAllOrders(List<Orders> orders, String status, int warehouseID, int userID) {
        FileOutputStream fileOut = null;
        String[] cellNames = {"Tracking number", "Order Date", "Order Status",
            "Warehouse", "User", "Address", "Shipping price", "Product price"};
        try {
            setCurrentRepDate();
            fileOut = new FileOutputStream(repAllOrdFileName + currentRepDate + FILE_ENDING, true);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("Report1");
            HSSFCellStyle tableUsual = workbook.createCellStyle();
            tableUsual = setUsualStyle(tableUsual);
            HSSFCellStyle tableHead = workbook.createCellStyle();
            tableHead = setHeadCellStyle(tableHead);
            HSSFCellStyle repHead = workbook.createCellStyle();
            repHead = setRepHeadStyle(repHead);
            HSSFCellStyle additionalCellStyle = workbook.createCellStyle();
            additionalCellStyle = setAdditionalCellsStyle(additionalCellStyle);

            HSSFRow headerRow = worksheet.createRow(0);
            HSSFCell header = headerRow.createCell(0);
            header.setCellValue("Report on all shipment orders without statistics");
            header.setCellStyle(repHead);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 15);
            worksheet.addMergedRegion(region);

            HSSFRow statusRow = worksheet.createRow(1);
            HSSFCell st = statusRow.createCell(0);
            st.setCellValue("Status:");
            st.setCellStyle(additionalCellStyle);
            HSSFCell stVal = statusRow.createCell(1);
            stVal.setCellValue(status);
            stVal.setCellStyle(tableUsual);
            region = new CellRangeAddress(1, 1, 1, 15);
            worksheet.addMergedRegion(region);

            HSSFRow idWarehouseRow = worksheet.createRow(2);
            HSSFCell idWar = idWarehouseRow.createCell(0);
            idWar.setCellValue("Warehouse:");
            idWar.setCellStyle(additionalCellStyle);
            HSSFCell idWarVal = idWarehouseRow.createCell(1);
            if (warehouseID == 0) {
                idWarVal.setCellValue("all warehouses");
            } else {
                idWarVal.setCellValue(warehouseFacade.find(warehouseID).getName());
            }
            idWarVal.setCellStyle(tableUsual);
            region = new CellRangeAddress(2, 2, 1, 15);
            worksheet.addMergedRegion(region);

            HSSFRow idUserRow = worksheet.createRow(3);
            HSSFCell idUs = idUserRow.createCell(0);
            idUs.setCellValue("User");
            idUs.setCellStyle(additionalCellStyle);
            HSSFCell idUsVal = idUserRow.createCell(1);
            if (userID == 0) {
                idUsVal.setCellValue("all users");
            } else {
                Users user = usersFacade.find(userID);
                idUsVal.setCellValue(user.getFirstName() + " " + user.getLastName());
            }
            idUsVal.setCellStyle(tableUsual);
            region = new CellRangeAddress(3, 3, 1, 15);
            worksheet.addMergedRegion(region);

            HSSFRow headRow = worksheet.createRow(4);
            HSSFCell headCell = null;
            int j=0;
            for (int i = 0; i < cellNames.length; i++) {
                headCell = headRow.createCell(j);
                headCell.setCellValue(cellNames[i]);
                headCell.setCellStyle(tableHead);
                headCell = headRow.createCell(j + 1);
                headCell.setCellStyle(tableHead);
                region = new CellRangeAddress(4, 4, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;
            }
            worksheet.createFreezePane(0, 5);
            HSSFRow row = null;
            int i = 5;
            for (Orders ord : orders) {
                j=0;
                row = worksheet.createRow(i);

                HSSFCell trNumCell = row.createCell(j);
                trNumCell.setCellValue(ord.getTrackingNumber());
                trNumCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell dateCell = row.createCell(j);
                dateCell.setCellValue(dateToString(ord.getOrderDate()));
                dateCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell statusCell = row.createCell(j);
                statusCell.setCellValue(ord.getOrderStatus());
                statusCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell wareCell = row.createCell(j);
                wareCell.setCellValue(ord.getIdWarehouse().getName());
                wareCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell usCell = row.createCell(j);
                Users user = ord.getIdUser();
                if (user == null) {
                    usCell.setCellValue("unregistered");
                } else {
                    usCell.setCellValue(ord.getIdUser().getFirstName() + " "
                            + ord.getIdUser().getLastName());
                }
                usCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell adCell = row.createCell(j);
                adCell.setCellValue(ord.getAddress());
                adCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell shPCell = row.createCell(j);
                shPCell.setCellValue(ord.getShippingPrice().doubleValue());
                shPCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                j+=2;

                HSSFCell prPCell = row.createCell(j);
                prPCell.setCellValue(ord.getProductPrice().doubleValue());
                prPCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i, i, j, j+1);
                worksheet.addMergedRegion(region);
                i++;
            }
            workbook.write(fileOut);
        } catch (FileNotFoundException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format! File not found!", e);
        } catch (IOException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", e);
        } finally {
            try {
                fileOut.flush();
                fileOut.close();
            } catch (IOException ex) {
                Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", ex);
            }
        }
    }

    public void exportRepOnOrdersStatistic(List<String> status, List<Integer> quantity, List<BigDecimal> summaryPrice,
                                                  int idUser, int idWarehouse) {
        FileOutputStream fileOut = null;
        String[] cellNames = {"Orders status", "Orders quantity", "Summary price"};
        try {
            //String date = new SimpleDateFormat("dd-MM-yyyy-hh-mm").format(new Date());
            String date = dateToString(new Date());
            //repStatisticOrdFileName = repStatisticOrdFileName + date + ".xls";
            setCurrentRepDate();
            fileOut = new FileOutputStream(repStatisticOrdFileName + currentRepDate + FILE_ENDING, true);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("Report1");
            HSSFCellStyle tableUsual = workbook.createCellStyle();
            tableUsual = setUsualStyle(tableUsual);
            HSSFCellStyle tableHead = workbook.createCellStyle();
            tableHead = setHeadCellStyle(tableHead);
            HSSFCellStyle repHead = workbook.createCellStyle();
            repHead = setRepHeadStyle(repHead);
            HSSFCellStyle additionalCell = workbook.createCellStyle();
            additionalCell = setAdditionalCellsStyle(additionalCell);

            HSSFRow headRow = worksheet.createRow(0);
            HSSFCell header = headRow.createCell(0);
            header.setCellValue("Report on all shipment orders");
            header.setCellStyle(repHead);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
            worksheet.addMergedRegion(region);

            headRow = worksheet.createRow(1);
            HSSFCell headCell = null;
            for (int i = 0, j = 0; i < cellNames.length; i++, j+=2) {
                headCell = headRow.createCell(j);
                headCell.setCellValue(cellNames[i]);
                headCell.setCellStyle(tableHead);
                headCell = headRow.createCell(j + 1);
                headCell.setCellStyle(tableHead);
                region = new CellRangeAddress(1, 1, j, j + 1);
                worksheet.addMergedRegion(region);
            }
            HSSFRow row = null;
            int i = 0;
            while(i < status.size()){
                int j = 0;
                row = worksheet.createRow(i + 2);

                HSSFCell stCell = row.createCell(j);
                stCell.setCellValue(status.get(i));
                stCell.setCellStyle(tableUsual);
                stCell = row.createCell(++j);
                stCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i+2, i+2, j-1, j);
                worksheet.addMergedRegion(region);
                j++;

                HSSFCell quaCell = row.createCell(j);
                quaCell.setCellValue(quantity.get(i));
                quaCell.setCellStyle(tableUsual);
                quaCell = row.createCell(++j);
                quaCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i+2, i+2, j-1, j);
                worksheet.addMergedRegion(region);
                j++;

                HSSFCell priceCell = row.createCell(j);
                priceCell.setCellValue(summaryPrice.get(i).doubleValue());
                priceCell.setCellStyle(tableUsual);
                priceCell = row.createCell(++j);
                priceCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i+2, i+2, j-1, j);
                worksheet.addMergedRegion(region);
                j++;
                i++;
            }
            i += 3;
            row = worksheet.createRow(i);
            HSSFCell idWare = row.createCell(0);
            idWare.setCellValue("Warehouse:");
            idWare.setCellStyle(additionalCell);
            idWare = row.createCell(1);
            idWare.setCellStyle(additionalCell);
            region = new CellRangeAddress(i, i, 0, 1);
            worksheet.addMergedRegion(region);
            HSSFCell idWareVal = row.createCell(2);
            if(idWarehouse == 0){
                idWareVal.setCellValue("all warehouses");
            }
            else{
                idWareVal.setCellValue(warehouseFacade.find(idWarehouse).getName());
            }
            idWareVal.setCellStyle(tableUsual);
            region = new CellRangeAddress(i, i, 2, 5);
            worksheet.addMergedRegion(region);
            i++;
            
            row = worksheet.createRow(i);
            HSSFCell idUs = row.createCell(0);
            idUs.setCellValue("User:");
            idUs.setCellStyle(additionalCell);
            idUs = row.createCell(1);
            idUs.setCellStyle(additionalCell);
            region = new CellRangeAddress(i, i, 0, 1);
            worksheet.addMergedRegion(region);
            HSSFCell idUsVal = row.createCell(2);
            if(idUser == 0){
                idUsVal.setCellValue("all users");
            }
            else{
                Users user = usersFacade.find(idUser);
                idUsVal.setCellValue(user.getFirstName() + " " + user.getLastName());
            }
            idUsVal.setCellStyle(tableUsual);
            region = new CellRangeAddress(i, i, 2, 5);
            worksheet.addMergedRegion(region);
            workbook.write(fileOut);
        } catch (FileNotFoundException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format! File not found!", e);
        } catch (IOException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", e);
        } finally {
            try {
                fileOut.flush();
                fileOut.close();
            } catch (IOException ex) {
                Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", ex);
            }
        }
    }

    public void exportRepOnProducts(List<String> name, List<BigDecimal> price, List<Integer> quantity) {
        FileOutputStream fileOut = null; /*устанавливаем значение файлового потока null*/
        String[] cellNames = {"Name of Product", "Price", "Number of sold products"};/* создаем массив в котором будт храниться заголовки колонок*/
        try {
            setCurrentRepDate();
            fileOut = new FileOutputStream(repProductFileName + currentRepDate + FILE_ENDING, true);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("Report1");
            HSSFCellStyle tableUsual = workbook.createCellStyle();
            tableUsual = setUsualStyle(tableUsual);
            HSSFCellStyle tableHead = workbook.createCellStyle();
            tableHead = setHeadCellStyle(tableHead);
            HSSFCellStyle repHead = workbook.createCellStyle();
            repHead = setRepHeadStyle(repHead);
            
            HSSFRow headRow = worksheet.createRow(0); /*создаем заголовочную строку*/
            HSSFCell header = headRow.createCell(0);/*объединяем ее в одну ячейку*/
            header.setCellValue("Report on most popular products"); /*заносим в нее заголовок*/
            header.setCellStyle(repHead);/*устанавливаем стиль объявленный выше*/
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 9);
            worksheet.addMergedRegion(region);

            headRow = worksheet.createRow(1);
            HSSFCell headCell = null;
            int j = 0;
            for (int i = 0; i < cellNames.length; i++) {
                headCell = headRow.createCell(j);
                headCell.setCellValue(cellNames[i]);
                headCell.setCellStyle(tableHead);
                if(i == 0){
                    region = new CellRangeAddress(1, 1, j, j += 4);
                }
                else if (i == 2){
                    region = new CellRangeAddress(1, 1, j, j += 2);
                }
                else{
                    region = new CellRangeAddress(1, 1, j, ++j);
                }
                worksheet.addMergedRegion(region);
                j++;
            }
            HSSFRow row = null;
            for (int i = 0; i < name.size(); i++) {
                j = 0;
                row = worksheet.createRow(i + 2);

                HSSFCell nmCell = row.createCell(j);
                region = new CellRangeAddress(i + 2, i + 2, j, j += 4);
                worksheet.addMergedRegion(region);
                nmCell.setCellValue(name.get(i));
                nmCell.setCellStyle(tableUsual);
                j++;

                HSSFCell prCell = row.createCell(j);
                prCell.setCellValue(price.get(i).toString());
                prCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i + 2, i + 2, j, ++j);
                worksheet.addMergedRegion(region);
                j++;
                
                HSSFCell quanCell = row.createCell(j);
                quanCell.setCellValue(quantity.get(i).doubleValue());
                quanCell.setCellStyle(tableUsual);
                region = new CellRangeAddress(i + 2, i + 2, j, j += 2);
                worksheet.addMergedRegion(region);
                j++;
            }
            workbook.write(fileOut);/*записываем данные до окончания файла */
            /*отлавливаем возможные ошибки. Эту часть кода скопировала у Олега*/
        } catch (FileNotFoundException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format! File not found!", e);
        } catch (IOException e) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", e);
        } finally {
            try {
                fileOut.flush(); /*полученные данные передаются для записи*/
          
                fileOut.close(); /*файл закрывается*/
            } catch (IOException ex) {
                Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not export report to excel format!", ex);
            }
        }
    }
/*Этот код писала не я*/
    public static void automaticallyOpenFile(String filename) { /*функция которая автоматически открывает файл с указанным именем*/
        String command = "cmd /c start " + filename; /*созадем строковую переменую, которая содержит путь к файлу на диске С*/
        try {
            Process child = Runtime.getRuntime().exec(command); /*запускаем в текущем времмени эту команду*/
        } catch (IOException ex) {
            Logger.getLogger(ExportRepToExcel.class.getName()).log(Level.SEVERE, "Could not open report file!", ex);
        }
    }

    private static HSSFCellStyle setRepHeadStyle(HSSFCellStyle cellStyle) { /*функция которая назначает стиль верхней строки*/
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return cellStyle;
    }

    private static HSSFCellStyle setHeadCellStyle(HSSFCellStyle cellStyle) {/*функция которая назначает стиль названий колонок*/
        cellStyle.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index); /*устанавливает фон*/
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); /*выравнивание текста*/
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);/*нижняя граница*/
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);/*верхняя граница*/
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);/*левая граница*/
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);/*правая граница*/
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/*высоту ячейки*/
    
        return cellStyle;  /*возвращает стиль ячейки*/
    }

    private static HSSFCellStyle setUsualStyle(HSSFCellStyle cellStyle) {/*функция которая назначает стиль всех остальных ячеек.*/
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);  /*эти значения аналогичны тем, что в предидущей функции толкьо с другими параметрами*/
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

    private static HSSFCellStyle setAdditionalCellsStyle(HSSFCellStyle cellStyle) {
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

//    private static HSSFWorkbook getWorkbookFromFile(String xlsFile) throws IOException {
//        System.gc();
//        return new HSSFWorkbook(new POIFSFileSystem(ExportRepToExcel.class.getResourceAsStream(xlsFile)));
//    }
/* функции, которые связывают клиентское приложение с сервером */
    private UsersFacadeLocal lookupUsersFacadeLocal() {
        try {
            Context c = new InitialContext();
            return (UsersFacadeLocal) c.lookup("java:global/Delta/Delta-ejb/UsersFacade!facade.UsersFacadeLocal");
        } catch (NamingException ne) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Exception caught!", ne);
            throw new RuntimeException(ne);
        }
    }

    private WarehouseFacadeLocal lookupWarehouseFacadeLocal() {
        try {
            Context c = new InitialContext(); 
            return (WarehouseFacadeLocal) c.lookup("java:global/Delta/Delta-ejb/WarehouseFacade!facade.WarehouseFacadeLocal");
        } catch (NamingException ne) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Exception caught!", ne);
            throw new RuntimeException(ne);
        }
    }
    
    private static String dateToString(Date date){
        return new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss").format(date);
    }
    
    private static void setStyleToManyItems(HSSFRow row, HSSFSheet sheet,
                                            int minCellNumber, int cellQuantity,
                                            HSSFCellStyle cellStyle){
        HSSFCell cell= null;
        for(int i=0; i<cellQuantity; i++){
            cell = row.createCell(minCellNumber + i);
            cell.setCellStyle(cellStyle);
        }
        int rowNum = row.getRowNum();
        CellRangeAddress region = new CellRangeAddress(rowNum, rowNum, minCellNumber, minCellNumber + cellQuantity);
        sheet.addMergedRegion(region);
    }
    
    private static void setCurrentRepDate(){
       currentRepDate = dateToString(new Date());
    }
}
